package com.guodu.dao.impl;

import java.text.DecimalFormat;
import java.text.ParseException;
import java.util.List;

import org.springframework.stereotype.Repository;

import com.guodu.base.BaseDao;
import com.guodu.dao.PurchasingInformationThreeDao;
import com.guodu.entity.PurchasingInformationThree;
import com.guodu.entity.PurchasinginforthreeStatus;
import com.guodu.entity.Units;
import com.guodu.vo.PurchasingInformationThreeQueryVo;
import com.guodu.vo.PurchasingInformationThreeVo;


@Repository("purchasingInformationThreeDao")
public class PurchasingInformationThreeDaoImpl extends BaseDao implements PurchasingInformationThreeDao {

	@Override
	public void save(PurchasingInformationThree purchasingInformationThree) {
		// TODO Auto-generated method stub
		super.save(purchasingInformationThree);
	}

	@Override
	public long findSizeByVo(PurchasingInformationThreeQueryVo pitq, Units units) {
		// TODO Auto-generated method stub
		StringBuffer hql = new StringBuffer("select count(*) from PurchasingInformationThree m where 1=1");
		if(pitq!=null){
			if (pitq.getFunitsId()!=null) {
				hql.append("    and exists(select 1 from Units u where u.unitsId = m.unitsId and u.funitsId  ="+pitq.getFunitsId()+" )");
			}
			if (pitq.getUnitsId()!=null) {
				hql.append("    and exists ( select 1 from UserInfo u where u.userId=m.createPeople and u.unitsId="+pitq.getUnitsId()+")");
			}
			if (pitq.getMaterialCode()!=null && !"".equals(pitq.getMaterialCode())) {
				hql.append("    and materialCode like '%"+pitq.getMaterialCode()+"%'");
			}
			if (pitq.getDrawingnum()!=null && !"".equals(pitq.getDrawingnum())) {
				hql.append("    and drawingnum like '%"+pitq.getDrawingnum()+"%'");
			}
			if (pitq.getMaterialName()!=null && !"".equals(pitq.getMaterialName())) {
				hql.append("    and materialName like '%"+pitq.getMaterialName()+"%'");
			}
			if (pitq.getProcurementMethod()!=null && !"".equals(pitq.getProcurementMethod())) {
				hql.append("    and purchaseType like '%"+pitq.getProcurementMethod()+"%'");
			}
			if (pitq.getSpecifications()!=null && !"".equals(pitq.getSpecifications())) {
				hql.append("    and specifications like '%"+pitq.getSpecifications()+"%'");
			}
			if (pitq.getSupplier()!=null && !"".equals(pitq.getSupplier())) {
				hql.append("    and supplier like '%"+pitq.getSupplier()+"%'");
			}
			if (pitq.getTexture()!=null && !"".equals(pitq.getTexture())) {
				hql.append("    and texture like '%"+pitq.getTexture()+"%'");
			}
			if (pitq.getTbbeginDate()!=null && !"".equals(pitq.getTbbeginDate())) {
				hql.append("    and createTime >= TO_DATE('"+pitq.getTbbeginDate()+" 00:00:00','yyyy-mm-dd hh24:mi:ss')");
			}
			if (pitq.getTbendDate()!=null && !"".equals(pitq.getTbendDate())) {
				hql.append("    and createTime <= TO_DATE('"+pitq.getTbendDate()+" 23:59:59','yyyy-mm-dd hh24:mi:ss')");
			}
			if (pitq.getMonths()!=null && !"".equals(pitq.getMonths())) {
				hql.append("    and TO_DATE(months,'yyyy-mm') >= TO_DATE('"+pitq.getMonths()+"','yyyy-mm')");
			}
			if (pitq.getEmonths()!=null && !"".equals(pitq.getEmonths())) {
				hql.append("    and TO_DATE(months,'yyyy-mm') <= TO_DATE('"+pitq.getEmonths()+"','yyyy-mm')");
			}
			if(pitq.getStatus()!=null&&pitq.getStatus().trim().length()>0&&!pitq.getStatus().equals("5")){
				hql.append(" and status ="+pitq.getStatus());
			}
		}
		if(units==null){
			hql.append("and status=1");
		}else if(units.getUnitsLevel()==1L&&units.getIsComplete()==1){
			hql.append("and status=1");
		}else if(units.getUnitsLevel()==1L&&units.getIsComplete()==0){
			hql.append("and (status=0 or status=1 or status=2 or status=3 or status=4)");
		}
		return super.findInt(hql.toString());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<PurchasingInformationThree> findByVo(
			PurchasingInformationThreeQueryVo pitq, Units units, int curPage,
			int pageSize) {
		// TODO Auto-generated method stub
		StringBuffer hql = new StringBuffer("from PurchasingInformationThree m where 1=1");
		if(pitq!=null){
			if (pitq.getFunitsId()!=null) {
				hql.append("    and exists(select 1 from Units u where u.unitsId = m.unitsId and u.funitsId  ="+pitq.getFunitsId()+" )");
			}
			if (pitq.getUnitsId()!=null) {
				hql.append("    and exists ( select 1 from UserInfo u where u.userId=m.createPeople and u.unitsId="+pitq.getUnitsId()+")");
			}
			if (pitq.getMaterialCode()!=null && !"".equals(pitq.getMaterialCode())) {
				hql.append("    and materialCode like '%"+pitq.getMaterialCode()+"%'");
			}
			if (pitq.getDrawingnum()!=null && !"".equals(pitq.getDrawingnum())) {
				hql.append("    and drawingnum like '%"+pitq.getDrawingnum()+"%'");
			}
			if (pitq.getMaterialName()!=null && !"".equals(pitq.getMaterialName())) {
				hql.append("    and materialName like '%"+pitq.getMaterialName()+"%'");
			}
			if (pitq.getProcurementMethod()!=null && !"".equals(pitq.getProcurementMethod())) {
				hql.append("    and purchaseType like '%"+pitq.getProcurementMethod()+"%'");
			}
			if (pitq.getSpecifications()!=null && !"".equals(pitq.getSpecifications())) {
				hql.append("    and specifications like '%"+pitq.getSpecifications()+"%'");
			}
			if (pitq.getSupplier()!=null && !"".equals(pitq.getSupplier())) {
				hql.append("    and supplier like '%"+pitq.getSupplier()+"%'");
			}
			if (pitq.getTexture()!=null && !"".equals(pitq.getTexture())) {
				hql.append("    and texture like '%"+pitq.getTexture()+"%'");
			}
			if (pitq.getTbbeginDate()!=null && !"".equals(pitq.getTbbeginDate())) {
				hql.append("    and createTime >= TO_DATE('"+pitq.getTbbeginDate()+" 00:00:00','yyyy-mm-dd hh24:mi:ss')");
			}
			if (pitq.getTbendDate()!=null && !"".equals(pitq.getTbendDate())) {
				hql.append("    and createTime <= TO_DATE('"+pitq.getTbendDate()+" 23:59:59','yyyy-mm-dd hh24:mi:ss')");
			}	
			if (pitq.getMonths()!=null && !"".equals(pitq.getMonths())) {
				hql.append("    and TO_DATE(months,'yyyy-mm') >= TO_DATE('"+pitq.getMonths()+"','yyyy-mm')");
			}
			if (pitq.getEmonths()!=null && !"".equals(pitq.getEmonths())) {
				hql.append("    and TO_DATE(months,'yyyy-mm') <= TO_DATE('"+pitq.getEmonths()+"','yyyy-mm')");
			}
			if(pitq.getStatus()!=null&&pitq.getStatus().trim().length()>0&&!pitq.getStatus().equals("5")){
				hql.append(" and status ="+pitq.getStatus());
			}
		}
		if(units==null){
			hql.append("and status=1");
		}else if(units.getUnitsLevel()==1L&&units.getIsComplete()==1){
			hql.append("and status=1");
		}else if(units.getUnitsLevel()==1L&&units.getIsComplete()==0){
			hql.append("and (status=0 or status=1 or status=2 or status=3 or status=4)");
		}
		hql.append(" order by purchaseId desc");
		return super.findPages(hql.toString(), curPage, pageSize);
	}

	@Override
	public PurchasingInformationThree findById(String purchaseId) {
		// TODO Auto-generated method stub
		return (PurchasingInformationThree) super.findById(PurchasingInformationThree.class, purchaseId);
	}

	@Override
	public void update(PurchasingInformationThree purchasingInformationThree) {
		// TODO Auto-generated method stub
		super.update(purchasingInformationThree);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<PurchasinginforthreeStatus> findStatus(String purchaseId) {
		// TODO Auto-generated method stub
		return super.findByProperty(PurchasinginforthreeStatus.class, "purchaseId", purchaseId);
	}

	@Override
	public void changeStatus(List<PurchasinginforthreeStatus> entities) {
		// TODO Auto-generated method stub
		super.saveAll(entities);
	}

	@Override
	public void examine(String ids, Long status) {
		// TODO Auto-generated method stub
		StringBuffer hql = new StringBuffer("update PurchasingInformationThree set status =");
		hql.append(status);
		hql.append(" where purchaseId in (");
		hql.append(ids);
		hql.append(")");
		super.executeUpdate(hql.toString());
	}

	@Override
	public void delByIds(String[] delIds) {
		// TODO Auto-generated method stub
		super.deleteAllById(PurchasingInformationThree.class, delIds);
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<PurchasingInformationThree> findAllByVo(
			PurchasingInformationThreeQueryVo pitq, Units units) {
		// TODO Auto-generated method stub
		StringBuffer hql = new StringBuffer("from PurchasingInformationThree m where 1=1");
		if(pitq!=null){
			if (pitq.getFunitsId()!=null) {
				hql.append("    and exists(select 1 from Units u where u.unitsId = m.unitsId and u.funitsId  ="+pitq.getFunitsId()+" )");
			}
			if (pitq.getUnitsId()!=null) {
				hql.append("    and exists ( select 1 from UserInfo u where u.userId=m.createPeople and u.unitsId="+pitq.getUnitsId()+")");
			}
			if (pitq.getMaterialCode()!=null && !"".equals(pitq.getMaterialCode())) {
				hql.append("    and materialCode like '%"+pitq.getMaterialCode()+"%'");
			}
			if (pitq.getDrawingnum()!=null && !"".equals(pitq.getDrawingnum())) {
				hql.append("    and drawingnum like '%"+pitq.getDrawingnum()+"%'");
			}
			if (pitq.getMaterialName()!=null && !"".equals(pitq.getMaterialName())) {
				hql.append("    and materialName like '%"+pitq.getMaterialName()+"%'");
			}
			if (pitq.getProcurementMethod()!=null && !"".equals(pitq.getProcurementMethod())) {
				hql.append("    and purchaseType like '%"+pitq.getProcurementMethod()+"%'");
			}
			if (pitq.getSpecifications()!=null && !"".equals(pitq.getSpecifications())) {
				hql.append("    and specifications like '%"+pitq.getSpecifications()+"%'");
			}
			if (pitq.getSupplier()!=null && !"".equals(pitq.getSupplier())) {
				hql.append("    and supplier like '%"+pitq.getSupplier()+"%'");
			}
			if (pitq.getTexture()!=null && !"".equals(pitq.getTexture())) {
				hql.append("    and texture like '%"+pitq.getTexture()+"%'");
			}
			if (pitq.getTbbeginDate()!=null && !"".equals(pitq.getTbbeginDate())) {
				hql.append("    and createTime >= TO_DATE('"+pitq.getTbbeginDate()+" 00:00:00','yyyy-mm-dd hh24:mi:ss')");
			}
			if (pitq.getTbendDate()!=null && !"".equals(pitq.getTbendDate())) {
				hql.append("    and createTime <= TO_DATE('"+pitq.getTbendDate()+" 23:59:59','yyyy-mm-dd hh24:mi:ss')");
			}	
			if (pitq.getMonths()!=null && !"".equals(pitq.getMonths())) {
				hql.append("    and TO_DATE(months,'yyyy-mm') >= TO_DATE('"+pitq.getMonths()+"','yyyy-mm')");
			}
			if (pitq.getEmonths()!=null && !"".equals(pitq.getEmonths())) {
				hql.append("    and TO_DATE(months,'yyyy-mm') <= TO_DATE('"+pitq.getEmonths()+"','yyyy-mm')");
			}
			if(pitq.getStatus()!=null&&pitq.getStatus().trim().length()>0&&!pitq.getStatus().equals("5")){
				hql.append(" and status ="+pitq.getStatus());
			}
		}
		if(units==null){
			hql.append("and status=1");
		}else if(units.getUnitsLevel()==1L&&units.getIsComplete()==1){
			hql.append("and status=1");
		}else if(units.getUnitsLevel()==1L&&units.getIsComplete()==0){
			hql.append("and (status=0 or status=1 or status=2 or status=3 or status=4)");
		}
		hql.append("order by createTime desc");
		return super.find(hql.toString());
	}

	@Override
	public PurchasingInformationThreeVo findAllMoney(
			PurchasingInformationThreeQueryVo pitq, Units units) {
		StringBuffer hql = new StringBuffer("select sum(purchaseAmount),sum(marketPrice),sum(purchaseSave) from PurchasingInformationThree m where 1=1");
		if(pitq!=null){
			if (pitq.getFunitsId()!=null) {
				hql.append("    and exists(select 1 from Units u where u.unitsId = m.unitsId and u.funitsId  ="+pitq.getFunitsId()+" )");
			}
			if (pitq.getUnitsId()!=null) {
				hql.append("    and exists ( select 1 from UserInfo u where u.userId=m.createPeople and u.unitsId="+pitq.getUnitsId()+")");
			}
			if (pitq.getMaterialCode()!=null && !"".equals(pitq.getMaterialCode())) {
				hql.append("    and materialCode like '%"+pitq.getMaterialCode()+"%'");
			}
			if (pitq.getDrawingnum()!=null && !"".equals(pitq.getDrawingnum())) {
				hql.append("    and drawingnum like '%"+pitq.getDrawingnum()+"%'");
			}
			if (pitq.getMaterialName()!=null && !"".equals(pitq.getMaterialName())) {
				hql.append("    and materialName like '%"+pitq.getMaterialName()+"%'");
			}
			if (pitq.getProcurementMethod()!=null && !"".equals(pitq.getProcurementMethod())) {
				hql.append("    and purchaseType like '%"+pitq.getProcurementMethod()+"%'");
			}
			if (pitq.getSpecifications()!=null && !"".equals(pitq.getSpecifications())) {
				hql.append("    and specifications like '%"+pitq.getSpecifications()+"%'");
			}
			if (pitq.getSupplier()!=null && !"".equals(pitq.getSupplier())) {
				hql.append("    and supplier like '%"+pitq.getSupplier()+"%'");
			}
			if (pitq.getTexture()!=null && !"".equals(pitq.getTexture())) {
				hql.append("    and texture like '%"+pitq.getTexture()+"%'");
			}
			if (pitq.getTbbeginDate()!=null && !"".equals(pitq.getTbbeginDate())) {
				hql.append("    and createTime >= TO_DATE('"+pitq.getTbbeginDate()+" 00:00:00','yyyy-mm-dd hh24:mi:ss')");
			}
			if (pitq.getTbendDate()!=null && !"".equals(pitq.getTbendDate())) {
				hql.append("    and createTime <= TO_DATE('"+pitq.getTbendDate()+" 23:59:59','yyyy-mm-dd hh24:mi:ss')");
			}	
			if (pitq.getMonths()!=null && !"".equals(pitq.getMonths())) {
				hql.append("    and TO_DATE(months,'yyyy-mm') >= TO_DATE('"+pitq.getMonths()+"','yyyy-mm')");
			}
			if (pitq.getEmonths()!=null && !"".equals(pitq.getEmonths())) {
				hql.append("    and TO_DATE(months,'yyyy-mm') <= TO_DATE('"+pitq.getEmonths()+"','yyyy-mm')");
			}
			if(pitq.getStatus()!=null&&pitq.getStatus().trim().length()>0&&!pitq.getStatus().equals("5")){
				hql.append(" and status ="+pitq.getStatus());
			}
		}
		if(units==null){
			hql.append("and status=1");
		}else if(units.getUnitsLevel()==1L&&units.getIsComplete()==1){
			hql.append("and status=1");
		}else if(units.getUnitsLevel()==1L&&units.getIsComplete()==0){
			hql.append("and (status=0 or status=1 or status=2 or status=3 or status=4)");
		}
		PurchasingInformationThreeVo p = new PurchasingInformationThreeVo();
		List<Object[]> objs = super.find(hql.toString());
		if(objs.size()>0){
			DecimalFormat df = new DecimalFormat("##,###.##");
			try {
				if(objs.get(0)[0]!=null){
					p.setAllMoney1(df.format(df.parse(objs.get(0)[0].toString())));
				}
                if(objs.get(0)[1]!=null){
                	p.setAllMoney2(df.format(df.parse(objs.get(0)[1].toString())));
				}
                if(objs.get(0)[2]!=null){
                	p.setAllMoney3(df.format(df.parse(objs.get(0)[2].toString())));
				}
                
			}catch (ParseException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}		
		return p;
	}

	@Override
	public void examin(PurchasingInformationThreeQueryVo pitq, Units units) {
		// TODO Auto-generated method stub
		StringBuffer hql = new StringBuffer("update PurchasingInformationThree m set status=1 where 1=1");
		if(pitq!=null){
			if (pitq.getFunitsId()!=null) {
				hql.append("    and exists(select 1 from Units u where u.unitsId = m.unitsId and u.funitsId  ="+pitq.getFunitsId()+" )");
			}
			if (pitq.getUnitsId()!=null) {
				hql.append("    and exists ( select 1 from UserInfo u where u.userId=m.createPeople and u.unitsId="+pitq.getUnitsId()+")");
			}
			if (pitq.getMaterialCode()!=null && !"".equals(pitq.getMaterialCode())) {
				hql.append("    and materialCode like '%"+pitq.getMaterialCode()+"%'");
			}
			if (pitq.getDrawingnum()!=null && !"".equals(pitq.getDrawingnum())) {
				hql.append("    and drawingnum like '%"+pitq.getDrawingnum()+"%'");
			}
			if (pitq.getMaterialName()!=null && !"".equals(pitq.getMaterialName())) {
				hql.append("    and materialName like '%"+pitq.getMaterialName()+"%'");
			}
			if (pitq.getProcurementMethod()!=null && !"".equals(pitq.getProcurementMethod())) {
				hql.append("    and purchaseType like '%"+pitq.getProcurementMethod()+"%'");
			}
			if (pitq.getSpecifications()!=null && !"".equals(pitq.getSpecifications())) {
				hql.append("    and specifications like '%"+pitq.getSpecifications()+"%'");
			}
			if (pitq.getSupplier()!=null && !"".equals(pitq.getSupplier())) {
				hql.append("    and supplier like '%"+pitq.getSupplier()+"%'");
			}
			if (pitq.getTexture()!=null && !"".equals(pitq.getTexture())) {
				hql.append("    and texture like '%"+pitq.getTexture()+"%'");
			}
			if (pitq.getTbbeginDate()!=null && !"".equals(pitq.getTbbeginDate())) {
				hql.append("    and createTime >= TO_DATE('"+pitq.getTbbeginDate()+" 00:00:00','yyyy-mm-dd hh24:mi:ss')");
			}
			if (pitq.getTbendDate()!=null && !"".equals(pitq.getTbendDate())) {
				hql.append("    and createTime <= TO_DATE('"+pitq.getTbendDate()+" 23:59:59','yyyy-mm-dd hh24:mi:ss')");
			}
			if (pitq.getMonths()!=null && !"".equals(pitq.getMonths())) {
				hql.append("    and TO_DATE(months,'yyyy-mm') >= TO_DATE('"+pitq.getMonths()+"','yyyy-mm')");
			}
			if (pitq.getEmonths()!=null && !"".equals(pitq.getEmonths())) {
				hql.append("    and TO_DATE(months,'yyyy-mm') <= TO_DATE('"+pitq.getEmonths()+"','yyyy-mm')");
			}
			if(pitq.getStatus()!=null&&pitq.getStatus().trim().length()>0&&!pitq.getStatus().equals("5")){
				hql.append(" and status ="+pitq.getStatus());
			}
		}
		if(units==null){
			hql.append("and status=1");
		}else if(units.getUnitsLevel()==1L&&units.getIsComplete()==1){
			hql.append("and status=1");
		}else if(units.getUnitsLevel()==1L&&units.getIsComplete()==0){
			hql.append("and (status=0 or status=1 or status=2 or status=3 or status=4)");
		}
		hql.append(" and status=0");
		super.executeUpdate(hql.toString());
	}

}
